 /**
   * Copyright 2014 Viettel Telecom. All rights reserved.
   * VIETTEL PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
   */
 package com.viettel.core.dao.cms; 
import java.util.ArrayList;
import java.util.List;

import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;

import com.viettel.core.common.utils.StringUtil;
import com.viettel.core.entities.cms.Users;
import com.viettel.core.entities.vo.UsersSearchFilter;
import com.viettel.core.entities.vo.UsersSearchVO;
import com.viettel.core.enumtype.UserStatus;
import com.viettel.core.exceptions.DataAccessException;
import com.viettel.core.repo.IRepository;
 /**
   *  Mo ta muc dich cua lop (interface)
   *  @author: TOANTM1
   *  @version:  
   *  @since: 27/02/2014 - 10:50:59
   */
 public class UsersDAOImpl implements UsersDAO { 

	private IRepository repo; 
	
 	public void setRepo(IRepository repo) {
		this.repo = repo;
	}

	/**@author TOANTM1
  	 * 27/02/2014 - 10:50:59
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public Users getUsersById(Integer id) throws DataAccessException { 
		return repo.getEntityById(Users.class, id); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 10:50:59
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public Users createUsers(Users users) 
			throws DataAccessException { 
		if (users == null) { 
			throw new IllegalArgumentException("users"); 
		} 
		users = repo.create(users); 
		//return repo.getEntityById(Users.class, users.getId());
		return users;
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 10:50:59
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void updateUsers(Users users) throws DataAccessException { 
		if (users == null) { 
			throw new IllegalArgumentException("users"); 
		} 
		repo.update(users); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 10:50:59
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void deleteUsers(Users users) throws DataAccessException { 
		if (users == null) { 
			throw new IllegalArgumentException("users"); 
		} 
		repo.delete(users); 
	}

	@Override
	public Boolean checkPhoneExist(String phone) throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		sql.append(" select count(1) count from dual where exists (select 1 from users where Upper(phone) = ? ");
		params.add(phone);
		sql.append(" and status != ? ");
		sql.append(" and status != ? ) ");
		params.add(UserStatus.DELETED.getValue());
		params.add(UserStatus.INACTIVE.getValue());
		int c = repo.countBySQL(sql.toString(), params);
		return c > 0 ? true : false;
	}
	@Override
	public Boolean checkPhoneExists(String phone) throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		sql.append(" select count(1) count from dual where exists (select 1 from users where Upper(phone) = ? ");
		params.add(phone);
		sql.append(" and status != ? ");
		sql.append(" and status != ? ) ");
		params.add(UserStatus.DELETED.getValue());
		params.add(UserStatus.INACTIVE.getValue());
		int c = repo.countBySQL(sql.toString(), params);
		return c > 1 ? true : false;
	}
	/**
	 * getListSearchUsersByFilter
	 * @author tuantm20
	 */
	@Override
	public List<UsersSearchVO> getListSearchUsersByFilter(
			UsersSearchFilter filter) throws DataAccessException {
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
			
		selectSql.append("SELECT us.id              AS id, ");
		selectSql.append("us.name                   AS name, ");
		selectSql.append("us.age                    AS age, ");
		selectSql.append("us.phone                  AS phone, ");
		selectSql.append("us.career                 AS career, ");
		selectSql.append("us.email                  AS email, ");
		selectSql.append("us.address                AS address, ");
		selectSql.append("us.status                 AS status, ");
		selectSql.append("us.gender                 AS gender, ");
		selectSql.append("us.province_id            AS provinceId, ");
		selectSql.append("pr.name                   AS provinceName, ");
		selectSql.append("us.district_id            AS districtId, ");
		selectSql.append("di.name                   AS districtName, ");
		selectSql.append("us.ward_id                AS wardId, ");
		selectSql.append("wa.name                   AS wardName, ");
		selectSql.append("us.media_id               AS mediaId, ");
		selectSql.append("me.name                   AS mediaName, ");
		selectSql.append("us.created_time           AS createdTime ");
		
		fromQuery.append("FROM   USERS us ");
		fromQuery.append("       LEFT JOIN PROVINCE pr ");
		fromQuery.append("              ON us.province_id = pr.id ");
		fromQuery.append("       LEFT JOIN DISTRICT di ");
		fromQuery.append("              ON us.district_id = di.id ");
		fromQuery.append("       LEFT JOIN WARD wa ");
		fromQuery.append("              ON us.ward_id = wa.id ");
		fromQuery.append("       LEFT JOIN MEDIA me ");
		fromQuery.append("              ON us.media_id = me.id ");
		
		fromQuery.append("WHERE us.status != ? ");		
		paramSelect.add(UserStatus.DELETED.getValue());
		countParams.add(UserStatus.DELETED.getValue());
		
		if (filter.getId() != null) {
			fromQuery.append("AND us.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getNameText())) {
			fromQuery.append("AND lower(us.name_text) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getPhone())) {
			fromQuery.append("AND lower(us.phone) LIKE lower(?) escape '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getPhone()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getPhone()) + "%");
		}
		
		if (filter.getProvinceId() != null) {
			fromQuery.append("AND us.province_id = ? ");
			paramSelect.add(filter.getProvinceId());
			countParams.add(filter.getProvinceId());
		}
		
		if (filter.getDistrictId() != null) {
			fromQuery.append("AND us.district_id = ? ");
			paramSelect.add(filter.getDistrictId());
			countParams.add(filter.getDistrictId());
		}
		
		if (filter.getWardId() != null) {
			fromQuery.append("AND us.ward_id = ? ");
			paramSelect.add(filter.getWardId());
			countParams.add(filter.getWardId());
		}
		
		if (filter.getMediaId() != null) {
			fromQuery.append("AND us.media_id = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}
		
		if (filter.getStatus()!=null) {
			fromQuery.append("AND us.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}
		if (filter.getEmail()!=null) {
			fromQuery.append("AND us.email = ? ");
			paramSelect.add(filter.getEmail());
			countParams.add(filter.getEmail());
		}
		
		if(filter.getFromDate()!=null && filter.getToDate()!=null ){
			fromQuery.append("  AND TRUNC(us.CREATED_TIME) BETWEEN TRUNC(?) AND TRUNC(?) ");
			paramSelect.add(filter.getFromDate());
			countParams.add(filter.getFromDate());
			
			paramSelect.add(filter.getToDate());
			countParams.add(filter.getToDate());
		}
		
		selectSql.append(fromQuery).append(" ORDER BY us.created_time DESC ");
		countQuery.append("SELECT COUNT(*) AS count ").append(fromQuery);
		String[] fieldNames = new String[] { 
				"id", "name", 
				"age", "phone", "status",
				"provinceId", "provinceName", "gender",
				"districtId", "districtName", "career",
				"wardId", "wardName",
				"mediaId", "mediaName" , 
				"createdTime", "email", "address"
				};
		
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING,  StandardBasicTypes.INTEGER,
				StandardBasicTypes.INTEGER,	StandardBasicTypes.STRING,  StandardBasicTypes.INTEGER, 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, 	StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING,
				StandardBasicTypes.TIMESTAMP,  StandardBasicTypes.STRING, StandardBasicTypes.STRING
				};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(Users.class);
		
		List<UsersSearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					UsersSearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(UsersSearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}
		return list;
	}

	@Override
	public List<Users> getListUsersByPhone(String phone, Integer maxRows)
			throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		String sql = "select * from users where phone like ? ";
		params.add("%" + phone + "%");
		if(maxRows!=null){
			sql += " and rownum < ? ";
			params.add(maxRows);
		}
		return repo.getListBySQL(Users.class, sql, params);
	}

	@Override
	public List<Users> getUsersByPhone(String phone) throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		String sql = "select * from users where status!= ? and phone = ? ";
		params.add(UserStatus.DELETED.getValue());
		params.add(phone);
		return repo.getListBySQL(Users.class, sql, params);
	}
} 
 
